using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class CPropriedades
   {
       private string IDT_PROP ;
       private string IDT_COM ;
       private string AREA ;
       private string STA_PROP ;
       private string NOME_PROP ;
       private string LATITUDE ;
       private string LONGITUDE ;
       private string COD_INCRA ;

	   /// <sumary>
       /// Construtor da classe propriedades
       /// <sumary>
       public CPropriedades()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Propriedades
       /// <sumary>
       public CPropriedades( string IDT_PROP, string IDT_COM, string AREA, string STA_PROP, string NOME_PROP, string LATITUDE, string LONGITUDE, string COD_INCRA )
       {
          this.IDT_PROP = IDT_PROP;
          this.IDT_COM = IDT_COM;
          this.AREA = AREA;
          this.STA_PROP = STA_PROP;
          this.NOME_PROP = NOME_PROP;
          this.LATITUDE = LATITUDE;
          this.LONGITUDE = LONGITUDE;
          this.COD_INCRA = COD_INCRA;
       }
	   
	   #region Propriedades
     	/// <sumary>
        /// Identificador da propriedade
        /// <sumary>
        public string Idt_prop 
		{
		   get
		   {
		       return this.IDT_PROP;
		   }
		   set
		   {
		       this.IDT_PROP = value;
		   }
		}
     	/// <sumary>
        /// Identificador da comunidade
        /// <sumary>
        public string Idt_com 
		{
		   get
		   {
		       return this.IDT_COM;
		   }
		   set
		   {
		       this.IDT_COM = value;
		   }
		}
     	/// <sumary>
        /// Area da propriedade
        /// <sumary>
        public string Area 
		{
		   get
		   {
		       return this.AREA;
		   }
		   set
		   {
		       this.AREA = value;
		   }
		}
     	/// <sumary>
        /// Situacao da propriedade: A - Ativa, I - Inativa
        /// <sumary>
        public string Sta_prop 
		{
		   get
		   {
		       return this.STA_PROP;
		   }
		   set
		   {
		       this.STA_PROP = value;
		   }
		}
     	/// <sumary>
        /// Nome da propriedade
        /// <sumary>
        public string Nome_prop 
		{
		   get
		   {
		       return this.NOME_PROP;
		   }
		   set
		   {
		       this.NOME_PROP = value;
		   }
		}
     	/// <sumary>
        /// Latitude da propriedade
        /// <sumary>
        public string Latitude 
		{
		   get
		   {
		       return this.LATITUDE;
		   }
		   set
		   {
		       this.LATITUDE = value;
		   }
		}
     	/// <sumary>
        /// Longitude da propriedade
        /// <sumary>
        public string Longitude 
		{
		   get
		   {
		       return this.LONGITUDE;
		   }
		   set
		   {
		       this.LONGITUDE = value;
		   }
		}
     	/// <sumary>
        /// Codigo da propriedade cadastrada no INCRA
        /// <sumary>
        public string Cod_incra 
		{
		   get
		   {
		       return this.COD_INCRA;
		   }
		   set
		   {
		       this.COD_INCRA = value;
		   }
		}
	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Propriedades.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_PROPRIEDADES
			                  ( IDT_PROP, IDT_COM, AREA, STA_PROP, NOME_PROP, LATITUDE, LONGITUDE, COD_INCRA )
						   values
						      ( :IDT_PROP, :IDT_COM, :AREA, :STA_PROP, :NOME_PROP, :LATITUDE, :LONGITUDE, :COD_INCRA )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_PROP",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_COM",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("AREA",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("STA_PROP",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("NOME_PROP",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("LATITUDE",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("LONGITUDE",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("COD_INCRA",OracleType.Number)); 
				
                command.Parameters["IDT_PROP"].Value = this.IDT_PROP;
                command.Parameters["IDT_COM"].Value = this.IDT_COM;
                command.Parameters["AREA"].Value = this.AREA;
                command.Parameters["STA_PROP"].Value = this.STA_PROP;
                command.Parameters["NOME_PROP"].Value = this.NOME_PROP;
                if (String.IsNullOrEmpty(this.LATITUDE))
                {
                    command.Parameters["LATITUDE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["LATITUDE"].Value = this.LATITUDE;
                }  
                if (String.IsNullOrEmpty(this.LONGITUDE))
                {
                    command.Parameters["LONGITUDE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["LONGITUDE"].Value = this.LONGITUDE;
                }  
                if (String.IsNullOrEmpty(this.COD_INCRA))
                {
                    command.Parameters["COD_INCRA"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["COD_INCRA"].Value = this.COD_INCRA;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Propriedades.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_PROPRIEDADES set 
			                   IDT_COM = :IDT_COM, AREA = :AREA, STA_PROP = :STA_PROP, NOME_PROP = :NOME_PROP, LATITUDE = :LATITUDE, LONGITUDE = :LONGITUDE, COD_INCRA = :COD_INCRA
						   where
						       IDT_PROP = :IDT_PROP ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_PROP",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_COM",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("AREA",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("STA_PROP",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("NOME_PROP",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("LATITUDE",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("LONGITUDE",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("COD_INCRA",OracleType.Number)); 
				
                command.Parameters["IDT_PROP"].Value = this.IDT_PROP;
                command.Parameters["IDT_COM"].Value = this.IDT_COM;
                command.Parameters["AREA"].Value = this.AREA;
                command.Parameters["STA_PROP"].Value = this.STA_PROP;
                command.Parameters["NOME_PROP"].Value = this.NOME_PROP;
                if (String.IsNullOrEmpty(this.LATITUDE))
                {
                    command.Parameters["LATITUDE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["LATITUDE"].Value = this.LATITUDE;
                }  
                if (String.IsNullOrEmpty(this.LONGITUDE))
                {
                    command.Parameters["LONGITUDE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["LONGITUDE"].Value = this.LONGITUDE;
                }  
                if (String.IsNullOrEmpty(this.COD_INCRA))
                {
                    command.Parameters["COD_INCRA"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["COD_INCRA"].Value = this.COD_INCRA;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Propriedades.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_PROPRIEDADES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_PROP", DbType.Int32, this.IDT_PROP));
            tab.delete();
        }
		
		static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_PROPRIEDADES order by NOME_PROP ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CPropriedades PesquisaporCodigo(string idt_prop, string login)
        {
            CPropriedades obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_PROPRIEDADES 
                            WHERE IDT_PROP = :IDT_PROP";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":IDT_PROP", DbType.String, idt_prop);
               
				qy.Executar();
                obj = new CPropriedades( qy.ObterCampo("IDT_PROP"), qy.ObterCampo("IDT_COM"), qy.ObterCampo("AREA"), qy.ObterCampo("STA_PROP"), qy.ObterCampo("NOME_PROP"), qy.ObterCampo("LATITUDE"), qy.ObterCampo("LONGITUDE"), qy.ObterCampo("COD_INCRA") );
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CPropriedades();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }
		
		static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_PROP() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }
		
		static public DataTable ObterPorIDT_COM(string idt_com)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_PROPRIEDADES  where IDT_COM = :IDT_COM order by NOME_PROP ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_COM", OracleType.Number));
                command.Parameters["IDT_COM"].Value = idt_com;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}	

